package mwa.cs.dao;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.Customer;
import mwa.cs.util.JDate;

/**
 * A class used for managing customer
 */
public class CustomerDAO {
	
    /** a database of this customer */
    private Database db;

    /**
     * Instantiate a new object of this class
     * @param db a database
     */
    public CustomerDAO(Database db) {
        this.db = db;
    }

    /**
     * a method for searching all customer in database
     * @return all customer
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM customer";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * a method for adding a customer into database
     * @param poCustomer customer to add
     */
    public void addCustomer(Customer poCustomer) {
        String vaSql = "INSERT INTO customer" + " (site_id, account, account_old, status, customer_type, customer_class, "
        		+ "pname, fname, lname, card_id, tax_id, tax_branch, house_id, home_number, address, city_code, "
        		+ "district_code, province_code, post_code, telephone, contact_home_number, contact_address, "
        		+ "contact_city_code, contact_district_code, contact_province_code, contact_post_code, contact_telephone, "
        		+ "meter_size, meter_brand, meter_serial_number, meter_number, meter_install_date, meter_date, "
        		+ "allowance, dept_amount, garbage_charge, wastewater_charge, other_charge, insert_user_id, insert_date, "
        		+ "insert_time, update_user_id, update_date, update_time)" 
        		+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
        				+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            db.insert(vaSql, poCustomer.getVnSiteId(),
            		poCustomer.getVaAccount(),
            		poCustomer.getVaAccountOld(),
            		poCustomer.getVaStatus(),
            		poCustomer.getVaCustomerType(),
            		poCustomer.getVaCustomerClass(),
            		poCustomer.getVaPName(),
            		poCustomer.getVaFName(),
            		poCustomer.getVaLName(),
            		poCustomer.getVaCardId(),
            		poCustomer.getVaTaxId(),
            		poCustomer.getVaTaxBranch(),
            		poCustomer.getVaHouseId(),
            		poCustomer.getVaHomeNumber(),
            		poCustomer.getVaAddress(),
            		poCustomer.getVaCityCode(),
            		poCustomer.getVaDistrictCode(),
            		poCustomer.getVaProvinceCode(),
            		poCustomer.getVaPostCode(),
            		poCustomer.getVaTelephone(),
            		poCustomer.getVaContactHomeNumber(),
            		poCustomer.getVaContactAddress(),
            		poCustomer.getVaContactCityCode(),
            		poCustomer.getVaContactDistrictCode(),
            		poCustomer.getVaContactProvinceCode(),
            		poCustomer.getVaContactPostCode(),
            		poCustomer.getVaContactTelephone(),
            		poCustomer.getVaMeterSize(),
            		poCustomer.getVaMeterBrand(),
            		poCustomer.getVaMeterSerialNumber(),
            		poCustomer.getVaMeterNumber(),
            		poCustomer.getVaMeterInstallDate(),
            		poCustomer.getVaMeterDate(),
            		poCustomer.getVaAllowance(),
            		poCustomer.getVnDeptAmount(),
            		poCustomer.getVnGarbageCharge(),
            		poCustomer.getVnWasteWaterCharge(),
            		poCustomer.getVnOtherCharge(),
            		poCustomer.getVnInsertUserId(),
            		poCustomer.getVaInsertDate(),
            		poCustomer.getVaInsertTime(),
            		poCustomer.getVnUpdateUserId(),
            		poCustomer.getVaUpdateDate(),
            		poCustomer.getVaUpdateTime());
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    /**
     * a method for updating a customer into database
     * @param pnSiteId site that customer live
     * @param paAccount that will update
     * @param poNewCustomer that will update
     */
    public void updateCustomer(int pnSiteId, String paAccount, Customer poNewCustomer) {
        String vaSql = "UPDATE customer SET account_old=?, "
        		+ "status=?, customer_type=?, customer_class=?, pname=?, fname=?, lname=?, "
        		+ "card_id=?, tax_id=?, tax_branch=?, house_id=?, home_number=?, address=?, "
        		+ "city_code=?, district_code=?, province_code=?, post_code=?, telephone=?, "
        		+ "contact_home_number=?, contact_address=?, contact_city_code=?, "
        		+ "contact_district_code=?, contact_province_code=?, contact_post_code=?, "
        		+ "contact_telephone=?, meter_size=?, meter_brand=?, meter_serial_number=?, "
        		+ "meter_number=?, meter_install_date=?, allowance=?, dept_amount=?, "
        		+ "garbage_charge=?, wastewater_charge=?, other_charge=?, "
        		+ "update_user_id=?, update_date=?, update_time=? WHERE site_id=? and account=?";
        try {
        	db.update(vaSql,
        			poNewCustomer.getVaAccountOld(),
        			poNewCustomer.getVaStatus(),
        			poNewCustomer.getVaCustomerType(),
        			poNewCustomer.getVaCustomerClass(),
        			poNewCustomer.getVaPName(),
        			poNewCustomer.getVaFName(),
        			poNewCustomer.getVaLName(),
        			poNewCustomer.getVaCardId(),
        			poNewCustomer.getVaTaxId(),
        			poNewCustomer.getVaTaxBranch(),
        			poNewCustomer.getVaHouseId(),
        			poNewCustomer.getVaHomeNumber(),
        			poNewCustomer.getVaAddress(),
        			poNewCustomer.getVaCityCode(),
        			poNewCustomer.getVaDistrictCode(),
        			poNewCustomer.getVaProvinceCode(),
        			poNewCustomer.getVaPostCode(),
        			poNewCustomer.getVaTelephone(),
        			poNewCustomer.getVaContactHomeNumber(),
        			poNewCustomer.getVaContactAddress(),
                	poNewCustomer.getVaContactCityCode(),
                	poNewCustomer.getVaContactDistrictCode(),
                	poNewCustomer.getVaContactProvinceCode(),
                	poNewCustomer.getVaContactPostCode(),
                	poNewCustomer.getVaContactTelephone(),
                	poNewCustomer.getVaMeterSize(), 
                	poNewCustomer.getVaMeterBrand(), 
                	poNewCustomer.getVaMeterSerialNumber(),
                	poNewCustomer.getVaMeterNumber(), 
                	poNewCustomer.getVaMeterInstallDate(), 
                	poNewCustomer.getVaAllowance(),
                	poNewCustomer.getVnDeptAmount(),
                	poNewCustomer.getVnGarbageCharge(),
                	poNewCustomer.getVnWasteWaterCharge(),
                	poNewCustomer.getVnOtherCharge(),
                	poNewCustomer.getVnUpdateUserId(), 
                	poNewCustomer.getVaUpdateDate(),
                	poNewCustomer.getVaUpdateTime(),
                	pnSiteId,
                	paAccount);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    /**
     * a method for counting customer by site_id
     * @param pnSiteId site that is wanted to count
     * @return total of counted
     */
    public int getAllCountCustomerBySiteId(int pnSiteId) {
        String vaSql = "SELECT count(*) FROM customer WHERE site_id=?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId);

        if (result != null) {
            return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    /**
     * find customer by site_id
     * @param pnSiteId site that is wanted to find customer
     * @return all customer in this site_id
     */
    public List<Map<String, Object>> findCustomerBySiteId(int pnSiteId, String paSortColumn, String paSortDirection) {
    	String vaSql = "SELECT * FROM customer WHERE site_id = ? ORDER BY " + paSortColumn + " " + paSortDirection + "";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * find customer by site_id, account
     * @param pnSiteId
     * @param paAccount
     * @return customer
     */
    public Customer findCustomerByAccount(int pnSiteId, String paAccount) {
    	String vaSql = "SELECT * FROM customer WHERE site_id = ? and account = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount);
        if (result != null) {
        	// insertUserId, UpdateUserId, DeptAmount is not to be "null" if it is null, error will be occur
        	String vaInsertUserId = ((BigDecimal) result.get("INSERT_USER_ID")).toString();
        	String vaUpdateUserId = ((BigDecimal) result.get("UPDATE_USER_ID")).toString();
        	String vaDeptAmount = ((BigDecimal) result.get("DEPT_AMOUNT")).toString();
        	String vaGarbageCharge = ((BigDecimal) result.get("GARBAGE_CHARGE")).toString();
        	String vaWasteWaterCharge = ((BigDecimal) result.get("WASTEWATER_CHARGE")).toString();
        	String vaOtherCharge = ((BigDecimal) result.get("OTHER_CHARGE")).toString();
        	int vnInsertUserId = 0, vnUpdateUserId = 0, vnDeptAmount = 0, vnGarbageCharge = 0,
        			vnWasteWaterCharge = 0, vnOtherCharge = 0;
        	if (vaInsertUserId != null) vnInsertUserId = Integer.parseInt(vaInsertUserId);
        	if (vaUpdateUserId != null) vnUpdateUserId = Integer.parseInt(vaUpdateUserId);
        	if (vaDeptAmount != null) vnDeptAmount = Integer.parseInt(vaDeptAmount);
        	if (vaGarbageCharge != null) vnGarbageCharge = Integer.parseInt(vaGarbageCharge);
        	if (vaWasteWaterCharge != null) vnWasteWaterCharge = Integer.parseInt(vaWasteWaterCharge);
        	if (vaOtherCharge != null) vnOtherCharge = Integer.parseInt(vaOtherCharge);
        	Customer voCustomer = new Customer(pnSiteId, paAccount, 
        			(String) result.get("ACCOUNT_OLD"),
        			(String) result.get("STATUS"),
        			(String) result.get("CUSTOMER_TYPE"),
        			(String) result.get("CUSTOMER_CLASS"),
        			(String) result.get("PNAME"),
        			(String) result.get("FNAME"),
        			(String) result.get("LNAME"),
        			(String) result.get("CARD_ID"),
        			(String) result.get("TAX_ID"),
        			(String) result.get("TAX_BRANCH"),
        			(String) result.get("HOUSE_ID"),
        			(String) result.get("HOME_NUMBER"),
        			(String) result.get("ADDRESS"),
        			(String) result.get("CITY_CODE"),
        			(String) result.get("DISTRICT_CODE"),
        			(String) result.get("PROVINCE_CODE"),
        			(String) result.get("POST_CODE"),
        			(String) result.get("TELEPHONE"),
        			(String) result.get("CONTACT_HOME_NUMBER"),
        			(String) result.get("CONTACT_ADDRESS"),
        			(String) result.get("CONTACT_CITY_CODE"),
        			(String) result.get("CONTACT_DISTRICT_CODE"),
        			(String) result.get("CONTACT_PROVINCE_CODE"),
        			(String) result.get("CONTACT_POST_CODE"),
        			(String) result.get("CONTACT_TELEPHONE"),
        			(String) result.get("METER_SIZE"),
        			(String) result.get("METER_BRAND"),
        			(String) result.get("METER_SERIAL_NUMBER"),
        			(String) result.get("METER_NUMBER"),
        			(String) result.get("METER_INSTALL_DATE"),
        			(String) result.get("METER_DATE"),
        			(String) result.get("ALLOWANCE"),
        			vnDeptAmount,
        			vnGarbageCharge,
        			vnWasteWaterCharge,
        			vnOtherCharge,
        			vnInsertUserId,
        			(String) result.get("INSERT_DATE"),
        			(String) result.get("INSERT_TIME"),
        			vnUpdateUserId,
        			(String) result.get("UPDATE_DATE"),
        			(String) result.get("UPDATE_TIME"));
        	if (!(voCustomer.getVaMeterInstallDate() == null)) 
        		voCustomer.setVaMeterInstallDate(JDate.showDate(voCustomer.getVaMeterInstallDate()));
            return voCustomer;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> findCustomerByLength(int pnSiteId, String paSortColumn, String paSortDirection, int pnStart, int pnEnd) {
    	String vaSql = "SELECT * FROM (SELECT account, account_old, pname, fname, lname, "
    			+ "home_number, address, city_code, district_code, province_code, post_code, ROWNUM rnum FROM "
    			+ "(SELECT * FROM customer WHERE  site_id = ? ORDER BY " + paSortColumn + " " + paSortDirection + ") "
    			+ "WHERE  ROWNUM <= ?) WHERE  rnum > ?";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnEnd, pnStart);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * a method for counting customer by searching
     * @param pnSiteId site that is wanted to count
     * @param paPName name for searching
     * @param paFName
     * @param paLName
     * @return total of counted
     */
    public int getSearchingCountCustomer(int pnSiteId, String paAccount, String paAccountOld,
    		String paHomeNumber, String paName) {
        String vaSql = "SELECT count(*) FROM customer WHERE site_id = ? "
        		+ "and (COALESCE(account, ' ') like '%" + paAccount + "%') "
        		+ "and (COALESCE(account_old, ' ') like '%" + paAccountOld + "%') "
        		+ "and (COALESCE(home_number, ' ') like '%" + paHomeNumber + "%') "
        		+ "and ((COALESCE(fname, ' ') like '%" + paName + "%') "
        		+ "or (COALESCE(lname, ' ') like '%" + paName + "%'))";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId);
        if (result != null) {
            return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    public List<Map<String, Object>> findCustomerBySearching(int pnSiteId, String paAccount, String paAccountOld,
    		String paHomeNumber, String paName, String paSortColumn, String paSortDirection, int pnStart, int pnEnd) {
    	// COALESCE = change null to " " 
    	String vaSql = "SELECT * FROM (SELECT account, account_old, pname, fname, lname, "
    			+ "home_number, address, city_code, district_code, province_code, post_code, ROWNUM rnum FROM "
    			+ "(SELECT * FROM customer WHERE  site_id = ? "
    			+ "and (COALESCE(account, ' ') like '%" + paAccount + "%') "
        		+ "and (COALESCE(account_old, ' ') like '%" + paAccountOld + "%') "
        		+ "and (COALESCE(home_number, ' ') like '%" + paHomeNumber + "%') "
    			+ "and ((COALESCE(fname, ' ') like '%" + paName + "%') "
    			+ "or (COALESCE(lname, ' ') like '%" + paName + "%')) "
    			+ "ORDER BY " + paSortColumn + " " + paSortDirection + ") "
    			+ "WHERE  ROWNUM <= ?) WHERE  rnum > ?";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnEnd, pnStart);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * delete customer by site_id, account
     * @param pnSiteId is an site_id that want to delete data
     * @param paAccount is an account that want to delete data
     * @return true if success, false if fail
     */
    public boolean deleteCustomerByAccount(int pnSiteId, String paAccount) {
        String vaSql = "DELETE FROM customer WHERE site_id = ? and account = ?";
        try {
            db.update(vaSql, pnSiteId, paAccount);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    
    /**
	 * finding Customer
	 * 
	 * @param paAcount
	 * @return Map<String, Object> Customer
	 */
	public Map<String, Object> getCustomer(int pnSiteId,String paAccount) {
		
			String vaSql = "SELECT customer.account,customer.pname||' '||customer.fname||' '||customer.lname AS name, "
					+ "customer.status,customer.home_number, customer.address, customer.city_code, customer.district_code, "
					+ "customer.province_code, customer.post_code ,customer.meter_serial_number,customer_class.description FROM customer,customer_class "
					+ "WHERE customer.customer_class = customer_class.code AND customer.site_id= ? AND customer.account = ? ";
			Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount);
			if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
	}
	
	// บันทึกข้อมูล Customer ในกรณีที่บันทึกผลการติดตั้งประปา
  	public boolean SaveCustomerForInstall(Customer poNewCustomer, String paRequestNumber) {
          String vaSql = "INSERT INTO customer ( "
        		  + "site_id, account, status, customer_type, customer_class, pname, fname, lname, card_id, house_id, home_number, address, "
        		  + "city_code, district_code, province_code, post_code, telephone, contact_home_number, contact_address, contact_city_code, "
        		  + "contact_district_code, contact_province_code, contact_post_code, contact_telephone, meter_size, meter_brand, "
        		  + "meter_serial_number, meter_number, meter_install_date, meter_date, allowance, dept_amount, garbage_charge, wastewater_charge, "
        		  + "other_charge, insert_user_id, insert_date, insert_time) "
        		  + "SELECT ?, account, ?, customer_type, customer_class, pname, fname, lname, card_id, house_id, home_number, address, "
        		  + "city_code, district_code, province_code, post_code, telephone, contact_home_number, contact_address, contact_city_code, "
        		  + "contact_district_code, contact_province_code, contact_post_code, contact_telephone, meter_size, ?, ?, ?, ?, ?, allowance, ?, "
        		  + "garbage_charge, wastewater_charge, other_charge, ?, ?, ? "
        		  + "FROM request WHERE request_number = ?";
        
          boolean result = false;
          try {
          	db.update(vaSql,
          			poNewCustomer.getVnSiteId(),
          			poNewCustomer.getVaStatus(),
          			poNewCustomer.getVaMeterBrand(),
          			poNewCustomer.getVaMeterSerialNumber(),
          			poNewCustomer.getVaMeterNumber(),
          			poNewCustomer.getVaMeterInstallDate(),
          			poNewCustomer.getVaMeterDate(),
          			poNewCustomer.getVnDeptAmount(),
          			poNewCustomer.getVnInsertUserId(),
          			poNewCustomer.getVaInsertDate(),
          			poNewCustomer.getVaInsertTime(),
          			paRequestNumber);
          	result = true;
          } catch (Exception e) {
          	System.out.println(e);
          	result = false;
          }
          return result;
  	}
    
}
